package work.chief.dao.impl;

import work.chief.dao.ProductDao;
import work.chief.entity.Product;
import work.chief.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    @Override
    public List<Product> selectByState(int state) {
        //操作数据库对象
        List<Product> productList=null;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="select * from product where state=?";
            productList=qr.query(sql,new BeanListHandler<Product>(Product.class),state);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return productList;
    }

    @Override
    public List<Product> selectByCName(String cname) {
        List<Product> productList=null;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="SELECT p.* from category c INNER JOIN product p ON c.cid=p.cid WHERE c.name like ?";
            productList=qr.query(sql,new BeanListHandler<Product>(Product.class),cname);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return productList;
    }

    @Override
    public Product selectByPid(int pid) {
        Product product=null;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="select * from product where pid=?";
            product=qr.query(sql,new BeanHandler<>(Product.class),pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return product;
    }

    @Override
    public List<Product> selectProductPage(String name, Integer state, String startTime, String endTime, Integer currentPage, Integer pageSize) {

        List<Product> productList =new ArrayList<>();

        try {
            //1=1 表示必然条件，输入为空时也可查询
            StringBuilder strb=new StringBuilder("select p.* ,(select c.name from category c where p.cid=c.cid) as cname from product p where 1=1");

            List<Object> parmList =new ArrayList<>();   //接入strb语句

            if (name!=null && !"".equals(name)){
                strb.append(" and name like ?");    //添加
                parmList.add("%"+name+"%");         //填充 ?
            }
            if (state!=null){
                strb.append(" and state =?");
                parmList.add(state);
            }
            if (startTime!=null && !"".equals(startTime)){
                strb.append(" and product_date >= ?");
                parmList.add(startTime);
            }
            if (endTime!=null && !"".equals(endTime)){
                strb.append(" and product_date <= ?");
                parmList.add(endTime);
            }
            strb.append(" limit ?,?");
            parmList.add((currentPage-1)*pageSize);
            parmList.add(pageSize);

            QueryRunner qr = DBUtils.getInstance().getQrDb();

            if (parmList!=null&&parmList.size()>0){
                productList=qr.query(strb.toString(),new BeanListHandler<>(Product.class),parmList.toArray());

            }else {
                productList=qr.query(strb.toString(),new BeanListHandler<>(Product.class));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return productList;
    }

    @Override
    public Integer selectProductCount(String name, Integer state, String startTime, String endTime) {

        Long count=null;

        try {
            StringBuilder strb=new StringBuilder("select count(1) from product p where 1=1");
            List<Object> parmList = new ArrayList<>();
            if (name!=null&&!"".equals(name)){
                strb.append(" and name like ?");
                parmList.add("%"+name+"%");
            }
            if (state!=null){
                strb.append(" and state =?");
                parmList.add(state);
            }
            if (startTime!=null&&!"".equals(startTime)){
                strb.append(" and product_date >= ?");
                parmList.add(startTime);
            }
            if (endTime!=null&&!"".equals(endTime)){
                strb.append(" and product_date <= ?");
                parmList.add(endTime);
            }
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            if (parmList !=null && parmList.size()>0){
                count = (Long)qr.query(strb.toString(), new ScalarHandler(), parmList.toArray());
            }else {
                count=(Long)qr.query(strb.toString(),new ScalarHandler());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Integer.parseInt(count+"");
    }

    @Override
    public int deleteByPid(String pid) {
        int num = 0;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="delete from product where pid in("+pid+")";
            num = qr.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateProduct(Product product) {

        int update = 0;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="update product set cid=?,name=?,color=?,size=?,price=?,description=?,state=?,version=?,product_date=? where pid=?";
            Object[] objects={product.getCid(),product.getName(),product.getColor(),product.getSize(),product.getPrice(),product.getDescription(),product.getState(),product.getVersion(),product.getProduct_date(),product.getPid()};
            update = qr.update(sql, objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return update;
    }

    @Override
    public int addProduct(Product product) {
        int add = 0;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="insert into product(cid,name,color,size,price,description,state,version,product_date,pic)values(?,?,?,?,?,?,?,?,?,?)";
            Object[] objects={product.getCid(),product.getName(),product.getColor(),product.getSize(),product.getPrice(),product.getDescription(),product.getState(),product.getVersion(),product.getProduct_date(),product.getPic()};
            add = qr.update(sql, objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return add;
    }


}
